<?php
       
       $investigador= utf8_decode($_GET['investigador']);
       $ininv=$_GET['ininv'];
       $finv=$_GET['finv'];
      
    /*   $investigador=  utf8_decode("Alicia González Manjarrez");
       $ininv=1962;
       $finv=2014;
       //$quotes='isi';
    */
       
       $fd=  fopen("archivos/datosinvut.txt", "w");
       $fd2= fopen("archivos/datospapers.txt","w");
        //Se almacena la conexion en la variable $con
        
        $con=mysqli_connect("192.168.1.10","srojas","anemona") or die("Failed to connect with database!!!!");
        mysqli_select_db( $con,"hermes2");
        
        
       

        $sth2=  mysqli_query($con,"SELECT  ci.ut_citado as ut, ci.titulo as title, ci.publicacion_year as anio FROM hermes2.citantes_isiwos as ci join  hermes2.papers_vs_citas as pc on pc.ut=ci.ut_citado
                            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join hermes2.stat_inv as st on p.id=st.id_inv where p.nombre='$investigador' and ci.publicacion_year>=$ininv 
                            and ci.publicacion_year<=$finv and pc.publicacion_year<st.contratacion_year and pc.doi is not null order by anio asc;");

        $sth3=  mysqli_query($con,"SELECT  ci.ut_citado as ut, ci.titulo as title, ci.publicacion_year as anio FROM hermes2.citantes_isiwos as ci join  hermes2.papers_vs_citas as pc on pc.ut=ci.ut_citado
                            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join hermes2.stat_inv as sta on p.id=sta.id_inv 
                            where p.nombre='$investigador' and ci.publicacion_year>=$ininv and ci.publicacion_year<=$finv and pc.publicacion_year>=sta.contratacion_year and pc.doi is not null order by anio asc;");
        
       
        $sth4=  mysqli_query($con,"SELECT ci.publicacion_year as ano, count(*) as citasss FROM hermes2.citantes_isiwos as ci join  hermes2.papers_vs_citas as pc on pc.ut=ci.ut_citado
                            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join hermes2.stat_inv as st on p.id=st.id_inv
                            where p.nombre='$investigador' and pc.publicacion_year>=st.contratacion_year and pc.doi is not null group by ci.publicacion_year HAVING publicacion_year>=$ininv and publicacion_year<=$finv;");
        
        $sth5 = mysqli_query($con, "select totales.anio as pub ,totales.citas as cpub ,ifc.citas as cpubi from
                            (SELECT ci.publicacion_year as anio, count(*) as citas FROM hermes2.citantes_isiwos as ci join  hermes2.papers_vs_citas as pc on pc.ut=ci.ut_citado
                            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join hermes2.stat_inv as st on p.id=st.id_inv
                            where p.nombre='$investigador' and pc.publicacion_year>=st.contratacion_year and pc.doi is not null group by ci.publicacion_year HAVING publicacion_year>=$ininv and publicacion_year<=$finv) as ifc
                            left outer join
                            (SELECT ci.publicacion_year as anio,count(*) as citas FROM hermes2.citantes_isiwos as ci join  hermes2.papers_vs_citas as pc on pc.ut=ci.ut_citado
                            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join hermes2.stat_inv as st on p.id=st.id_inv
                            where p.nombre='$investigador' and pc.publicacion_year<st.contratacion_year and pc.doi is not null group by ci.publicacion_year HAVING publicacion_year>=$ininv and publicacion_year<=$finv) as totales
                            on totales.anio=ifc.anio
                            union
                            select totales.anio as pub ,totales.citas as cpub ,ifc.citas as cpubi from
                            (SELECT ci.publicacion_year as anio, count(*) as citas FROM hermes2.citantes_isiwos as ci join  hermes2.papers_vs_citas as pc on pc.ut=ci.ut_citado
                            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join hermes2.stat_inv as st on p.id=st.id_inv
                            where p.nombre='$investigador' and pc.publicacion_year>=st.contratacion_year and pc.doi is not null group by ci.publicacion_year HAVING publicacion_year>=$ininv and publicacion_year<=$finv)as ifc
                            right outer join
                            (SELECT ci.publicacion_year as anio,count(*) as citas FROM hermes2.citantes_isiwos as ci join  hermes2.papers_vs_citas as pc on pc.ut=ci.ut_citado
                            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join hermes2.stat_inv as st on p.id=st.id_inv
                            where p.nombre='$investigador' and pc.publicacion_year<st.contratacion_year and pc.doi is not null group by ci.publicacion_year HAVING publicacion_year>=$ininv and publicacion_year<=$finv)as totales
                            on totales.anio=ifc.anio
                            order by pub asc");
        
            $rows = array();
            $rows2=array();
            $table = array();
            $table['cols'] = array(

            // Labels for your chart, these represent the column titles
            // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
            array('label' => 'publicacion_year', 'type' => 'string'),
            array('label' => 'Citas_IFC','type' => 'number'),
            array('label' => 'Citas', 'type' => 'number')
            
            );
            
           
            
            fwrite($fd2,$investigador."\r\n");
            fwrite($fd2, "publicación_year  ut  titulo"."\r\n");
            while($r = mysqli_fetch_assoc($sth2)) {
                fwrite($fd2, $r['anio']."\t");
                fwrite($fd2,"\t".$r['ut']."\t");
                fwrite($fd2,"\t".$r['title']."\r\n");
                
            }
            fwrite($fd2,"Publicaciones IFC \r\n");
            while($r=  mysqli_fetch_assoc($sth3)){
                fwrite($fd2, $r['anio']."\t");
                fwrite($fd2,"\t".$r['ut']."\t");
                fwrite($fd2,"\t".$r['title']."\r\n");
                
            }
           
            

            while($r = mysqli_fetch_assoc($sth4)) {
            $temp2 = array();
            // the following line will be used to slice the Pie chart
            $temp2[] = array('v' => (string) utf8_encode($r['ano'])); 
            
            // Values of each slice
            $temp2[] = array('v' => (int) $r['citasss']);  
             //echo json_encode($temp);  
                
                
                
                
            $rows2[] = array('c' => $temp2);
            }
            
          // echo json_encode($rows2);
            //$table2['rows']=$rows2;
        
            fwrite($fd,$investigador."\r\n");
            fwrite($fd, "publicacion_year   num_citas   num_citas_ifc"."\r\n");
            while($r = mysqli_fetch_assoc($sth5)) {
                fwrite($fd,"\t".$r['pub']."\t");
                fwrite($fd, $r['cpub']."\t");
                fwrite($fd, $r['cpubi']."\r\n");
              
            $temp = array();
            
            // the following line will be used to slice the Pie chart
            $temp[] = array('v' => (string) utf8_encode($r['pub']));
            
            // Values of each slice
                $temp[] = array('v' => (int) $r['cpubi']);
                $temp[] = array('v' => (int) $r['cpub']);
                
                   
           
                    
            $rows[] = array('c' => $temp);
            }
            
            
           
            for($i=0;$i<sizeof($rows2);$i++){
                for($j=0;$j<sizeof($rows);$j++){
                   
                    if($rows[$j]['c'][0] === array('v'=>"") && $rows[$j]['c'][1]===$rows2[$i]['c'][1]){
                        
                        $rows[$j]['c'][0]=$rows2[$i]['c'][0];
                        
                    }
                    
                }
                
                
            }
            sort($rows);
            
            $table['rows'] = $rows;
            echo json_encode($table);
           
            



            fclose($fd);
            fclose($fd2);
    

  mysqli_close($con);
 
        //echo json_encode($temp2)
        
        ?>